package bid.dao.impl;

import bid.dao.UserDao;
import bid.entity.Page;
import bid.entity.User;
import bid.util.DBUtil;
import bid.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author oldliu
 * @since 1.0
 */
//mysql的实现
public class UserDaoImpl implements UserDao {
    @Override
    public int add(User u) {
        u.setPassword(StringUtils.md5(u.getPassword()));

        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "insert into user" +
                "(userName,password,name,telephone,unit,role,enterdate)";
        sql += "values(?,?,?,?,?,?,?)";
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setString(1, u.getUsername());
            st.setString(2, u.getPassword());
            st.setString(3, u.getName());
            st.setString(4, u.getTelephone());
            st.setString(5, u.getUnit());
            st.setLong(6, u.getRoleid());
            st.setDate(7, u.getEnterdate() == null ? null : new java.sql.Date(u.getEnterdate().getTime()));
            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }


    @Override
    public int update(User u) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "update user set " +
                "userName = ?,password = ?,name = ?,telephone = ?,unit = ?, role = ?,enterdate = ?";
        sql += " where id = ?";

        try {
            //sql参数
            st = con.prepareStatement(sql);
            //填充占位符
            st.setString(1, u.getUsername());
            st.setString(2, u.getPassword());
            st.setString(3, u.getName());
            st.setString(4, u.getTelephone());
            st.setString(5, u.getUnit());
            st.setLong(6, u.getRoleid());
            st.setDate(7, u.getEnterdate() == null ? null : new java.sql.Date(u.getEnterdate().getTime()));
            st.setLong(8, u.getId());
            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }


    @Override
    public int del(long id) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "delete FROM user where id = ?";

//        sql += " where id = ?";
        try {
            //sql参数
            st = con.prepareStatement(sql);
            //填充占位符

            st.setLong(1, id);
            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public User get(long id) {
        String sql = "select * from user where id=?";

        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setLong(1, id);
            rs = st.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                user.setUsername(rs.getString("username"));
                user.setTelephone(rs.getString("telephone"));
                user.setEnterdate(rs.getDate("enterdate"));
                user.setUnit(rs.getString("unit"));
                user.setLandMark(rs.getString("landmark"));
                user.setIdcard(rs.getString("idcard"));
                user.setRoleid(rs.getLong("role"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return null;
    }

//    @Override
//    public List<User> search(int pageNo, int pageSize) {
//        String sql = "select * from user  limit ?,?";
//        List<User> users = new ArrayList<>();
//        Connection con = DBUtil.get();
//        PreparedStatement st = null;
//        ResultSet rs = null;
//        try {
//            st = con.prepareStatement(sql);//sql参数
//            //填充占位符
//            st.setLong(1, (pageNo - 1) * pageSize);
//            st.setLong(2, pageSize);
//            rs = st.executeQuery();
//            while (rs.next()) {
//                User user = new User();
//                user.setId(rs.getLong("id"));
//                user.setName(rs.getString("name"));
//                user.setUsername(rs.getString("username"));
//                user.setTelephone(rs.getString("telephone"));
//                users.add(user);
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        } finally {
//            DBUtil.close(rs, st, con);
//        }
//        return users;
//    }

    @Override
    public List<User> search(int pageNo, int pageSize, String name, String username, Date start, Date end) {
        String sql = "SELECT id,userName,name,telephone,unit," +
                "role,(select name from userrole where id=user.role)as rolename" +
                ",landMark,enterdate,idcard FROM user WHERE 1=1";
        List<Object> params = new ArrayList<>();
        if (StringUtils.isNotBlank(name)) {
            sql += " AND name LIKE ?";
            params.add("%" + name + "%");
        }
        if (StringUtils.isNotBlank(username)) {
            sql += " AND username LIKE ?";
            params.add("%" + username + "%");
        }
        if (start != null) {
            sql += " AND enterdate >= ?";
            params.add(start);
        }
        if (end != null) {
            sql += " AND enterdate <= ?";
            params.add(end);
        }
        sql += " ORDER BY enterdate DESC";
        sql += " LIMIT ?, ?";
        params.add((pageNo - 1) * pageSize);
        params.add(pageSize);
        System.out.println(sql);
        System.out.println(params);
        List<User> users = new ArrayList<>();
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            for (int i = 0; i < params.size(); i++) {
                st.setObject(i+1,params.get(i));
            }
            rs = st.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                user.setUsername(rs.getString("username"));
                user.setTelephone(rs.getString("telephone"));
                user.setEnterdate(rs.getDate("enterdate"));
                user.setUnit(rs.getString("unit"));
                user.setLandMark(rs.getString("landmark"));
                user.setIdcard(rs.getString("idcard"));
                user.setRoleid(rs.getLong("role"));
                user.setRolename(rs.getString("rolename"));
                //user.setPassword(rs.getString("password"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return users;
    }

    @Override
    public long searchTotal(String name, String username, Date start, Date end) {
        String sql = "select count(id) from user where 1=1";
        List<Object> params = new ArrayList<>();
        if (StringUtils.isNotBlank(name)) {
            sql += " AND name LIKE ?";
            params.add("%" + name + "%");
        }
        if (StringUtils.isNotBlank(username)) {
            sql += " AND username LIKE ?";
            params.add("%" + username + "%");
        }
        if (start != null) {
            sql += " AND enterdate >= ?";
            params.add(start);
        }
        if (end != null) {
            sql += " AND enterdate <= ?";
            params.add(end);
        }

        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            for (int i = 0; i < params.size(); i++) {
                st.setObject(i+1,params.get(i));
            }
            rs = st.executeQuery();
            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return 0L;
    }

    @Override
    public User login(String u, String p) {
        p=StringUtils.md5(p);//加密一下
        String sql = "SELECT * FROM user WHERE username=? and password=?";


        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setString(1,u);
            st.setString(2,p);
            rs = st.executeQuery();
            if (rs.next()) {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                user.setUsername(rs.getString("username"));
                user.setTelephone(rs.getString("telephone"));
                user.setEnterdate(rs.getDate("enterdate"));
                user.setUnit(rs.getString("unit"));
                user.setLandMark(rs.getString("landmark"));
                user.setIdcard(rs.getString("idcard"));
                user.setRoleid(rs.getLong("role"));
                //user.setPassword(rs.getString("password"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return null;
    }

    @Override
    public Page<User> paging(int pageNo, int pageSize, String name, String username, Date start, Date end) {
        List<User> users=this.search(pageNo, pageSize, name, username, start, end);
        long total=this.searchTotal(name, username, start, end);
        return new Page<User>(pageNo,pageSize,total,users);
    }
}
